package com.yhp.dao.impl;

import com.yhp.bean.Express;
import com.yhp.dao.BaseExpressDao;
import com.yhp.util.DateFormatUtil;
import com.yhp.util.Dbutils;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoMysql extends Dbutils implements BaseExpressDao {
    //查询取件码是否重复
    public static final String SQL_LOOK_CODE = "SELECT CODE FROM EXPRESS WHERE STATUS=0 and code=?";
    //查询  全部快递data1_size数量   data1_day新增的数量           待取件快递data2_size数量    data2_day当天新增的快递并为取件
    public static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day,COUNT(STATUS=0 OR NULL) data2_size,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW())AND STATUS=0 OR NULL) data2_day FROM EXPRESS";
    //查询全部快递
    public static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
    //查询全部快递并分页
    public static final String SQL_FIND_ALL_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
    //根据取货码查询快递
    public static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
    //根据快递单号查询快递
    public static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
    //根据收件人手机号查询快递
    public static final String SQL_FIND_BY_PHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
    //根据收件人手机号查询快递
    public static final String SQL_FIND_BY_PHONE_AND_STATUS = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND STATUS=?";
    //根据录入人手机号查询快递
    public static final String SQL_FIND_BY_SYSPHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
    //快递新增
    public static final String SQL_INSERT = "INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,now(),?,?)";
    //快递修改
    public static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,USERPHONE=?,COMPANY=?,CODE=?";
    //快递的状态修改 (取件)
    public static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET STATUS=1,OUTTIME=NOW() WHERE CODE = ?";
    //快递的删除
    public static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";


    /**
     * 查找取件码是否重复
     *
     * @param code 取件码
     * @return 重复返回  false   不重复  返回true
     */
    @Override
    public boolean lookCode(String code) {
        List list = new ArrayList();
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
             state= connection.prepareStatement(SQL_LOOK_CODE);
             state.setString(1,code);
             resultSet= state.executeQuery();
           if(resultSet.next()){
               return false;
           }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }
      return true;
    }

    /**
     * 用于查询数据库中的全部快递(总数+新增)，待取件快递(总数+新增)
     *
     * @return {{size:总数,day:新增},{size:总数,day:新增}}
     */
    @Override
    public List<Map<String, Integer>> console() {
        ArrayList<Map<String,Integer>> data = new ArrayList<>();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            //2.预编译sql语句
            state = connection.prepareStatement(SQL_CONSOLE);
            //3.（可选）填充参数

            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()){
                int data1_size = resultSet.getInt("data1_size");
                int data1_day = resultSet.getInt("data1_day");
                int data2_size = resultSet.getInt("data2_size");
                int data2_day = resultSet.getInt("data2_day");
                Map data1 = new HashMap();
                data1.put("data1_size",data1_size);
                data1.put("data1_day",data1_day);
                Map data2 = new HashMap();
                data2.put("data2_size",data2_size);
                data2.put("data2_day",data2_day);

                data.add(data1);
                data.add(data2);


            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭io流
            closeall();
        }


        return data;
    }

    /**
     * 用于查询所有快递
     *
     * @param limit      是否分页  true表示分页  false表示查询全部
     * @param offset     SQL语句的起始索引
     * @param pageNumber 页查询的数量
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        List<Express> list = new ArrayList<>();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            if (limit) {
                //2.预编译sql语句
                state = connection.prepareStatement(SQL_FIND_ALL_LIMIT);
                //3.（可选）填充参数
                state.setInt(1, offset);
                state.setInt(2, pageNumber);
            } else {
                state = connection.prepareStatement(SQL_FIND_ALL);
            }

            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                list.add(express);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //6.关闭io流
            closeall();
        }
        return list;
    }

    /**
     * 根据快递单号查询快递信息
     *
     * @param number 单号
     * @return 查询的快递信息     单号不存在时返回null
     */
    @Override
    public Express findByNumber(String number) {
        Express express = new Express();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            //2.预编译sql语句
            state = connection.prepareStatement(SQL_FIND_BY_NUMBER);
            //3.（可选）填充参数
            state.setString(1,number);
            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()){
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                return express;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭io流
            closeall();
        }
        return null;
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return 查询的快递信息     取货吗不存在时返回null
     */
    @Override
    public Express findByCode(String code) {
        Express express = new Express();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            //2.预编译sql语句
            state = connection.prepareStatement(SQL_FIND_BY_CODE);
            //3.（可选）填充参数
            state.setString(1,code);
            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()){
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭io流
            closeall();
        }
        return express;
    }

    /**
     * 根据手机号查询查询快递信息
     *
     * @param phone 手机号
     * @return 查询的快递信息
     */
    @Override
    public List<Express> findByPhone(String phone) {
        List<Express> list = new ArrayList<>();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            //2.预编译sql语句
            state = connection.prepareStatement(SQL_FIND_BY_PHONE);
            //3.（可选）填充参数
            state.setString(1,phone);
            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()){
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                list.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭io流
            closeall();
        }
        return list;
    }

    /**
     * 根据手机号查询查询快递信息
     *
     * @param phone  手机号
     * @param status 状态码
     * @return 查询的快递信息
     */
    @Override
    public List<Express> findByPhoneAndStatus(String phone, int status) {
        List<Express> list = new ArrayList<>();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            //2.预编译sql语句
            state = connection.prepareStatement(SQL_FIND_BY_PHONE_AND_STATUS);
            //3.（可选）填充参数
            state.setString(1,phone);
            state.setInt(2,status);
            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()){
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                list.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭io流
            closeall();
        }
        return list;

    }

    /**
     * 根据录入人手机号查询查询快递信息
     *
     * @param sysPhone 手机号
     * @return 查询的快递信息
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List<Express> list = new ArrayList<>();
        //1.获取数据库链接
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        ResultSet resultSet = null;
        try {
            //2.预编译sql语句
            state = connection.prepareStatement(SQL_FIND_BY_SYSPHONE);
            //3.（可选）填充参数
            state.setString(1,sysPhone);
            //4.执行sql语句
            resultSet = state.executeQuery();
            //5.获取数据
            while (resultSet.next()){
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setUserName(resultSet.getString("username"));
                express.setUserPhone(resultSet.getString("userphone"));
                express.setCompany(resultSet.getString("company"));
                express.setSysPhone(resultSet.getString("sysphone"));
                express.setCode(resultSet.getString("code"));
                express.setStatus(resultSet.getInt("status"));
                express.setInTime(resultSet.getTimestamp("intime"));
                express.setOutTime(resultSet.getTimestamp("outtime"));
                list.add(express);

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //6.关闭io流
            closeall();
        }
        return list;
    }

    /***
     * 快递的录入
     * @param e  录入的对象
     * @return 返回  录入成功或者失败
     */
    @Override
    public boolean insert(Express e) {
        int i = 0;
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        try {
            //INSERT INTO EXPRESS
            // (NUMBER,USERNAME,USERPHONE,COMPANY,CODE,SYSPHONE)
            // VALUES(?,?,?,?,?,?)
            state = connection.prepareStatement(SQL_INSERT);
            state.setString(1,e.getNumber());
            state.setString(2,e.getUserName());
            state.setString(3,e.getUserPhone());
            state.setString(4,e.getCompany());
            state.setString(5,e.getCode());
            state.setInt(6,e.getStatus()==1?1:0);
            state.setString(7,e.getSysPhone());
            i = state.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }finally {
            closeall();
        }
        return i > 0;
    }


    /**
     * 快递的修改
     *
     * @param id   要修改的快递的id
     * @param newe 新的快递对象  修改的数据
     * @return 返回成功或者失败
     */
    @Override
    public boolean update(int id, Express newe) {
        boolean delete = delete(id);
        boolean insert = insert(newe);
        return delete && insert;
    }

    /***
     * 更改快递的状态为1  表示取件完成
     * @param code  要更改快递的id
     * @return 就改的结果  成功或者失败
     */
    @Override
    public boolean updateStatus(String code) {
        int i = 0;
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        try {
            state = connection.prepareStatement(SQL_UPDATE_STATUS);
            state.setString(1,code);
            i = state.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeall();
        }

        return i>0;
    }

    /***
     * 删除快递
     * @param id  要删除快递的id
     * @return 返回成功或者失败
     */
    @Override
    public boolean delete(int id) {
        int i = 0;
        Connection connection = Dbutils.getConnection();
        PreparedStatement state = null;
        try {
            state = connection.prepareStatement(SQL_DELETE);
            state.setInt(1,id);
            i = state.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i>0;
    }
}
